﻿using System;
using System.Collections;
using MySql.Data.MySqlClient;

namespace Payroll
{
    public partial class MySqlPayrollDatabase: InMemoryPayrollDatabase
    {
        public static MySqlPayrollDatabase Instance => Holder.HOLDER;
        private MySqlPayrollDatabase()
        {
            Init();
        }

        private void Init()
        {
            string sql = "SELECT * FROM employee";
            MySqlConnection conn = DBUtil.GetConnection();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Employee employee = new Employee(int.Parse(reader[0].ToString()),
                    reader[1].ToString(),
                    reader[2].ToString());
                AddEmployeeTransaction addEmployeeTx;

                int classificationItem = int.Parse(reader[5].ToString());
                // 1 临时工 2月工 3绩效
                if (classificationItem == 1)
                {
                    addEmployeeTx = new AddHourlyEmployee(employee.EmpId,
                        employee.Name,
                        employee.Address,
                        double.Parse(reader[6].ToString()),
                        this);
                }
                else if (classificationItem == 2)
                {
                    addEmployeeTx = new AddSalariedEmployee(employee.EmpId,
                        employee.Name,
                        employee.Address,
                        double.Parse(reader[6].ToString()),
                        this);
                }
                else
                {
                    addEmployeeTx = new AddCommissionedEmployee(employee.EmpId,
                        employee.Name,
                        employee.Address,
                        double.Parse(reader[6].ToString()),
                        double.Parse(reader[7].ToString()),
                        this);
                }
                addEmployeeTx.Execute();
                // 更新(无/有)工会都实现了这个接口
                ChangeAffiliationTransaction changeAffiliationTx;

                int affiliationItem = Convert.ToInt32(reader[3].ToString());
                // 0 无  1有
                if (affiliationItem == 1)
                {
                    changeAffiliationTx = new ChangeMemberTransaction(employee.EmpId,
                        Convert.ToInt32(reader[8].ToString()),
                        1.0D,
                        this);
                }
                else
                {
                    changeAffiliationTx = new ChangeUnaffiliatedTransaction(employee.EmpId,
                        this);
                }
                changeAffiliationTx.Execute();

                // 更新发薪方式都实现了这个接口
                ChangeMethodTransaction changeMethodTx;
                
                int methodItem = Convert.ToInt32(reader[4].ToString());

                // 1 直接存款 2 支票 3 邮寄
                if (methodItem == 1)
                {
                    changeMethodTx = new ChangeDirectTransaction(employee.EmpId, this);
                }
                else if (methodItem == 2)
                {
                    changeMethodTx = new ChangeHoldTransaction(employee.EmpId, this);
                }
                else
                {
                    changeMethodTx = new ChangeMailTransaction(employee.EmpId, this);
                }
                changeMethodTx.Execute();
            }
            reader.Close();
            conn.Close();
            balking = true;
        }

        public bool Balking
        {
            get { return balking; }
        }

        
        public void SaveStatus()
        {
            if (balking)
            {
                return;
            }
            foreach (DictionaryEntry de in employees)
            {
                MySqlConnection conn = DBUtil.GetConnection();
                conn.Open();
                string sql = "SELECT * FROM employee WHERE emp_id = @emp_id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Add(new MySqlParameter("@emp_id", de.Key));
                MySqlDataReader reader = cmd.ExecuteReader();
                Employee e = de.Value as Employee;
                if (!reader.Read())
                {
                    reader.Close();
                    string insert = "INSERT employee(emp_id,name,address,affiliation,method,classification,bateRate,commissionRate,member_id) VALUES(@emp_id," +
                                     "@name," +
                                     "@address," +
                                     "@affiliation," +
                                     "@method," +
                                     "@classification," +
                                    "@bateRate," +
                                    "@commissionRate," +
                                    "@memberId)";
                    int classification = e.Classification is HourlyClassification
                            ? 1
                            : (e.Affiliation is SalariedClassification ? 2 : 3);
                    int method = e.Method is DirectDepositMethod ? 1 : (e.Method is HoldMethod ? 2 : 3);
                    int affiliation = e.Affiliation is NoAffiliation ? 0 : 1;
                    double baseRate = 0.0D;
                    double commissionRate = 0.0D;
                    int memberId = -1;
                    if (classification == 1 )
                    {
                        baseRate = (e.Classification as HourlyClassification).HourlyRate;
                    }
                    else if (classification == 2)
                    {
                        baseRate = (e.Classification as SalariedClassification).Salary;
                    }
                    else
                    {
                        baseRate = (e.Classification as CommissionClassification).BaseRate;
                        commissionRate = (e.Classification as CommissionClassification).CommissionRate;
                    }

                    if (affiliation == 1)
                    {
                        memberId = (e.Affiliation as UnionAffiliation).MemberId;
                    }
                    MySqlCommand cmdInner = new MySqlCommand(insert, conn);
                    cmdInner.Parameters.Add(new MySqlParameter("@emp_id", e.EmpId));
                    cmdInner.Parameters.Add(new MySqlParameter("@name", e.Name));
                    cmdInner.Parameters.Add(new MySqlParameter("@address", e.Address));
                    cmdInner.Parameters.Add(new MySqlParameter("@affiliation", affiliation));
                    cmdInner.Parameters.Add(new MySqlParameter("@method", method));
                    cmdInner.Parameters.Add(new MySqlParameter("@classification", classification));
                    cmdInner.Parameters.Add(new MySqlParameter("@bateRate", baseRate));
                    cmdInner.Parameters.Add(new MySqlParameter("@commissionRate", commissionRate));
                    cmdInner.Parameters.Add(new MySqlParameter("@memberId", memberId));
                    cmdInner.ExecuteNonQuery();
                }
                else
                {
                    reader.Close();
                    string update = "UPDATE employee SET name = @name " +
                                    " AND address = @address " +
                                    " AND affiliation = @affiliation " +
                                    " AND method = @method " +
                                    " AND classification = @classification " +
                                    " AND bateRate = @bateRate " +
                                    " AND commissionRate = @commissionRate" +
                                    " AND member_id = @memberId" +
                                    " WHERE emp_id = @emp_id ";
                    int classification = e.Classification is HourlyClassification
                        ? 1
                        : (e.Affiliation is SalariedClassification ? 2 : 3);
                    int method = e.Method is DirectDepositMethod ? 1 : (e.Method is HoldMethod ? 2 : 3);
                    int affiliation = e.Affiliation is NoAffiliation ? 0 : 1;
                    double bateRate = 0.0D;
                    double commissionRate = 0.0D;
                    int memberId = -1; 
                    if (classification == 1)
                    {
                        bateRate = (e.Classification as HourlyClassification).HourlyRate;
                    }
                    else if (classification == 2)
                    {
                        bateRate = (e.Classification as SalariedClassification).Salary;
                    }
                    else
                    {
                        bateRate = (e.Classification as CommissionClassification).BaseRate;
                        commissionRate = (e.Classification as CommissionClassification).CommissionRate;
                    }
                    if (affiliation == 1)
                    {
                        memberId = (e.Affiliation as UnionAffiliation).MemberId;
                    }
                    MySqlCommand cmdInner = new MySqlCommand(update, conn);
                    cmdInner.Parameters.Add(new MySqlParameter("@emp_id", e.EmpId));
                    cmdInner.Parameters.Add(new MySqlParameter("@name", e.Name));
                    cmdInner.Parameters.Add(new MySqlParameter("@address", e.Address));
                    cmdInner.Parameters.Add(new MySqlParameter("@affiliation", affiliation));
                    cmdInner.Parameters.Add(new MySqlParameter("@method", method));
                    cmdInner.Parameters.Add(new MySqlParameter("@classification", classification));
                    cmdInner.Parameters.Add(new MySqlParameter("@bateRate", bateRate));
                    cmdInner.Parameters.Add(new MySqlParameter("@commissionRate", commissionRate));
                    cmdInner.Parameters.Add(new MySqlParameter("@memberId", memberId));
                    cmdInner.ExecuteNonQuery();
                }
                conn.Close();
            }
            balking = true;
        }

        public override void DeleteInDisk(int id)
        {
            string sql = "DELETE FROM employee WHERE emp_id = @emp_id";
            MySqlConnection conn = DBUtil.GetConnection();
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Add(new MySqlParameter("@emp_id", id));
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}

